WITH tab   AS ( SELECT           waybill_no,
                                network_code,
                                network_type,
                                all_cnt ,
                                network_later_scantime,
                                network_early_scantime,
                                network_later_scantype,
                                network_early_scantype,
                                actual_taking_scantime,
                                actual_taking_weight,
                                actual_taking_scantype,
                                actual_taking_scanuser,
                                taking_later_scantime,
                                taking_early_scantime,
                                taking_early_weight,
                                taking_early_scanuser,
                                warehousing_later_scantime,
                                warehousing_early_scantime,
                                warehousing_early_weight,
                                warehousing_early_scanuser,
                                bagging_early_scantime,
                                bagging_later_scantime,
                                bagging_later_package_code,
                                unloading_later_scantime,
                                unloading_early_scantime,
                                unloading_early_weight,
                                arrival_later_scantime,
                                arrival_early_scantime,
                                arrival_early_weight,
                                arrival_early_scanuser,

                                unloading_arrival_shipment_no,
                                unloading_arrival_planned_departure_time,
                                unloading_arrival_planned_arrival_time,
                                unloading_arrival_actual_departure_time,
                                unloading_arrival_actual_arrival_time,
                                unloading_arrival_previous_network_code,
                                unloading_arrival_previous_network_type,

                                loading_early_scantime,
                                loading_later_scantime,
                                loading_later_weight,
                                send_early_scantime,
                                send_later_scantime,
                                send_later_weight,
                                send_later_scanuser,

                                loading_send_shipment_no,
                                loading_send_planned_departure_time,
                                loading_send_planned_arrival_time,
                                loading_send_actual_departure_time,
                                loading_send_actual_arrival_time,
                                loading_send_next_network_code,
                                loading_send_next_network_type,

                                deliver_early_scantime,
                                deliver_later_scantime,
                                deliver_later_weight,
                                deliver_later_scanuser,
                                sign_later_scantime,
                                sign_early_scantime,
                                sign_early_weight,
                                sign_early_scanuser,
                                actual_sign_scantime,
                                actual_sign_weight,
                                actual_sign_scantype,
                                actual_sign_scanuser,
                                aging_sign_scantime,
                                aging_sign_weight,
                                aging_sign_scantype,
                                aging_sign_scanuser,
                                unbagging_early_scantime,
                                unbagging_later_scantime,
                                unbagging_later_package_code,
                                dt,
                                row_number() over(partition by waybill_no,network_code order by dt desc) as dt_rank
                        from jms_dwd.dwd_s01_whole_operations_basic_mid_dt
                          where dt between date_add('{{ execution_date | cst_ds }}',-16) and '{{ execution_date | cst_ds }}'
        ),
tab_result   AS ( SELECT      waybill_no,
                                network_code,
                                network_type,
                                all_cnt ,
                                network_later_scantime,
                                network_early_scantime,
                                network_later_scantype,
                                network_early_scantype,
                                actual_taking_scantime,
                                actual_taking_weight,
                                actual_taking_scantype,
                                actual_taking_scanuser,
                                taking_later_scantime,
                                taking_early_scantime,
                                taking_early_weight,
                                taking_early_scanuser,
                                warehousing_later_scantime,
                                warehousing_early_scantime,
                                warehousing_early_weight,
                                warehousing_early_scanuser,
                                bagging_early_scantime,
                                bagging_later_scantime,
                                bagging_later_package_code,
                                unloading_later_scantime,
                                unloading_early_scantime,
                                unloading_early_weight,
                                arrival_later_scantime,
                                arrival_early_scantime,
                                arrival_early_weight,
                                arrival_early_scanuser,

                                unloading_arrival_shipment_no,
                                unloading_arrival_planned_departure_time,
                                unloading_arrival_planned_arrival_time,
                                unloading_arrival_actual_departure_time,
                                unloading_arrival_actual_arrival_time,
                                unloading_arrival_previous_network_code,
                                unloading_arrival_previous_network_type,

                                loading_early_scantime,
                                loading_later_scantime,
                                loading_later_weight,
                                send_early_scantime,
                                send_later_scantime,
                                send_later_weight,
                                send_later_scanuser,

                                loading_send_shipment_no,
                                loading_send_planned_departure_time,
                                loading_send_planned_arrival_time,
                                loading_send_actual_departure_time,
                                loading_send_actual_arrival_time,
                                loading_send_next_network_code,
                                loading_send_next_network_type,

                                deliver_early_scantime,
                                deliver_later_scantime,
                                deliver_later_weight,
                                deliver_later_scanuser,
                                sign_later_scantime,
                                sign_early_scantime,
                                sign_early_weight,
                                sign_early_scanuser,
                                actual_sign_scantime,
                                actual_sign_weight,
                                actual_sign_scantype,
                                actual_sign_scanuser,
                                aging_sign_scantime,
                                aging_sign_weight,
                                aging_sign_scantype,
                                aging_sign_scanuser,
                                unbagging_early_scantime,
                                unbagging_later_scantime,
                                unbagging_later_package_code,
                                dt,

          row_number() over(partition by waybill_no order by network_early_scantime asc )                     as all_rank_asc ,        -- 所有的网点，中心升序排序
          row_number() over(partition by waybill_no order by network_early_scantime desc )                    as all_rank_desc ,       -- 所有的网点，中心降序排序
          if(network_type=2,dense_rank() over(partition by waybill_no order by if(network_type=2,network_early_scantime,'2099-12-31 23:23:59') asc),0) as center_rank_asc ,      --  =1 可以取最早的转运中心
          if(network_type=2,dense_rank() over(partition by waybill_no order by if(network_type=2,network_early_scantime,'1970-01-01 00:00:00') desc),0) as center_rank_desc,     --  =1 可以取最晚的转运中心
          if(network_type=3,dense_rank() over(partition by waybill_no order by if(network_type=3,network_early_scantime,'2099-12-31 23:23:59') asc),0) as entreport_rank_asc,    --  =1 可以取最早的集散中心
          if(network_type=3,dense_rank() over(partition by waybill_no order by if(network_type=3,network_early_scantime,'1970-01-01 00:00:00') desc),0) as entreport_rank_desc,  --  =1 可以取最晚的集散中心
          lag(named_struct('network_code',network_code,'network_later_scantime',network_later_scantime)) over (partition by waybill_no order by network_early_scantime) as previous_cols ,
          lead(named_struct('network_code',network_code,'network_early_scantime',network_early_scantime)) over (partition by waybill_no order by network_early_scantime) as next_cols
                        from tab
                          where dt_rank = 1
),
network     AS (select
                           code,
                           name
                from jms_dim.dim_lmdm_sys_network
)
insert overwrite table jms_dwd.dwd_s01_whole_operations_basic_agg_dt partition(dt)
                         SELECT waybill_no,
                                network_code,
                                net1.name           as  network_name ,
                                network_type,

                                previous_cols.network_code as previous_network_code,
                                previous_cols.network_later_scantime as previous_later_scantime,
                                next_cols.network_code as next_network_code,
                                next_cols.network_early_scantime as next_early_scantime,

                                all_rank_asc ,
                                all_rank_desc ,
                                center_rank_asc,
                                center_rank_desc,
                                entreport_rank_asc,
                                entreport_rank_desc,
                                all_cnt ,
                                network_later_scantime,
                                network_early_scantime,
                                network_later_scantype,
                                network_early_scantype,

                                actual_taking_scantime,
                                actual_taking_weight,
                                actual_taking_scantype,
                                actual_taking_scanuser,
                                taking_later_scantime,
                                taking_early_scantime,
                                taking_early_weight,
                                taking_early_scanuser,
                                warehousing_later_scantime,
                                warehousing_early_scantime,
                                warehousing_early_weight,
                                warehousing_early_scanuser,
                                bagging_early_scantime,
                                bagging_later_scantime,
                                bagging_later_package_code,
                                unloading_later_scantime,
                                unloading_early_scantime,
                                unloading_early_weight,
                                arrival_later_scantime,
                                arrival_early_scantime,
                                arrival_early_weight,
                                arrival_early_scanuser,

                                unloading_arrival_shipment_no,
                                unloading_arrival_planned_departure_time,
                                unloading_arrival_planned_arrival_time,
                                unloading_arrival_actual_departure_time,
                                unloading_arrival_actual_arrival_time,
                                unloading_arrival_previous_network_code,
                                unloading_arrival_previous_network_type,

                                loading_early_scantime,
                                loading_later_scantime,
                                loading_later_weight,
                                send_early_scantime,
                                send_later_scantime,
                                send_later_weight,
                                send_later_scanuser,

                                loading_send_shipment_no,
                                loading_send_planned_departure_time,
                                loading_send_planned_arrival_time,
                                loading_send_actual_departure_time,
                                loading_send_actual_arrival_time,
                                loading_send_next_network_code,
                                net2.name  as  loading_send_next_network_name,
                                loading_send_next_network_type,

                                deliver_early_scantime,
                                deliver_later_scantime,
                                deliver_later_weight,
                                deliver_later_scanuser,
                                sign_later_scantime,
                                sign_early_scantime,
                                sign_early_weight,
                                sign_early_scanuser,
                                actual_sign_scantime,
                                actual_sign_weight,
                                actual_sign_scantype,
                                actual_sign_scanuser,
                                aging_sign_scantime,
                                aging_sign_weight,
                                aging_sign_scantype,
                                aging_sign_scanuser,
                                unbagging_early_scantime,
                                unbagging_later_scantime,
                                unbagging_later_package_code,
                                dt
                        from tab_result
                              left join network net1 on tab_result.network_code=net1.code
                              left join network net2 on coalesce(tab_result.loading_send_next_network_code,tab_result.waybill_no)=net2.code
                              distribute by dt,pmod(hash(rand()),36);